﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Edit_MasterPODetails : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        public string poid;
        public string order;
        
        public frm_Edit_MasterPODetails()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.poid = null;
            this.order = null;
            getTrasportIDList();
            this.transportationID.Properties.AutoComplete = true;
            getDeliveryList();
            this.deliveryID.Properties.AutoComplete = true;

            this.quantityValue.LostFocus += quantityValue_LostFocus;
            this.priceValue.LostFocus += priceValue_LostFocus;
        }

        void priceValue_LostFocus(object sender, EventArgs e)
        {
            float price = 0;
            if (!string.IsNullOrEmpty(this.priceValue.Text))
            {
                if (float.TryParse(this.priceValue.Text, out price))
                {
                    if (price <= 0)
                    {
                        MessageBox.Show("Price must be greater than 0");
                    }
                }
                else
                {
                    MessageBox.Show("Invalid price");
                }
            }
        }

        void quantityValue_LostFocus(object sender, EventArgs e)
        {
            float quantity = 0;
            if (!string.IsNullOrEmpty(this.quantityValue.Text))
            {
                if (float.TryParse(this.quantityValue.Text, out quantity))
                {
                    if (quantity <= 0)
                    {
                        MessageBox.Show("Quantity must be greater than 0");
                    }
                }
                else
                {
                    MessageBox.Show("Invalid quantity");
                }
            }
        }

        ////////////////////////////////////////////
        /// Load data
        ////////////////////////////////////////////
        public bool LoadData()
        {
            bool ret = true;

            if (string.IsNullOrEmpty(poid) || string.IsNullOrEmpty(order))
            {
                ret = false;
            }
            else
            {                
                try
                {
                    float quantity = 0;
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT * FROM [241_MasterPODetails]"
                        + " WHERE POID='" + poid + "' AND OrderNo='" + order + "' AND Cancelled=0";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {                        
                        this.poID.Text = dataReader["POID"].ToString();
                        quantity = float.Parse(dataReader["Quantity"].ToString());
                        this.quantityValue.Text = quantity.ToString();
                        this.unitID.Text = dataReader["UnitID"].ToString();
                        this.orderNo.Text = dataReader["OrderNo"].ToString();
                        this.partNo.Text = dataReader["PartNo"].ToString();
                        this.priceValue.Text = dataReader["UnitPrice"].ToString();
                        this.currencySymbol.Text = dataReader["CurrencyID"].ToString();
                        
                        if (!string.IsNullOrEmpty(dataReader["DeliveryDate"].ToString()))
                        {
                            this.deliveryDate.EditValue = DateTime.Parse(dataReader["DeliveryDate"].ToString());
                        }

                        for (int i = 0; i < this.transportationID.Properties.Items.Count; i++)
                        {
                            ComboboxItem item = (ComboboxItem)this.transportationID.Properties.Items[i];
                            if (item.Value.ToString() == dataReader["TransportID"].ToString())
                            {
                                this.transportationID.SelectedIndex = i;
                            }
                        }

                        for (int i = 0; i < this.deliveryID.Properties.Items.Count; i++)
                        {
                            ComboboxItem item = (ComboboxItem)this.deliveryID.Properties.Items[i];
                            if (item.Value.ToString() == dataReader["DeliveryTermID"].ToString())
                            {
                                this.deliveryID.SelectedIndex = i;
                            }
                        }
                        this.purposeValue.Text = dataReader["Purpose"].ToString();
                        this.noteValue.Text = dataReader["Note"].ToString();

                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();                    
                    
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    ret = false;
                }
            }
            return ret;
        }
        
        ////////////////////////////////////////////////
        /// Get TransportID List to form
        //////////////////////////////////////////////// 
        private void getTrasportIDList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT TransportID, TransportName FROM [222_Transport] ORDER BY TransportName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["TransportName"].ToString();
                    item.Value = dataReader["TransportID"].ToString();
                    this.transportationID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Trasportation list");
            }
        }

        ////////////////////////////////////////////////
        /// Load Delivery list to form
        ////////////////////////////////////////////////
        private void getDeliveryList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT DeliveryTermID, DeliveryTermName FROM [222_DeliveryTerm] ORDER BY DeliveryTermName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["DeliveryTermName"].ToString();
                    item.Value = dataReader["DeliveryTermID"].ToString();
                    this.deliveryID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Delivery list");
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            string error = null;

            float quantity = 0;
            string unit = null;
            float price = 0;
            string currency = null;
            DateTime deliverydate = new DateTime();
            string transport = null;
            string delivery = null;
            string purpose = null;
            string note = null;
            string partno = null;

            ComboboxItem item = new ComboboxItem();            

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.quantityValue.Text))
                {
                    error = "Please enter Quantity";
                }
                else
                {
                    if (!float.TryParse(this.quantityValue.Text, out quantity))
                    {
                        error = "Invalid quantity";
                    }
                    else
                    {
                        if (quantity <= 0)
                        {
                            error = "The quantity must be greater than 0.";
                        }
                    }
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.priceValue.Text))
                {
                    error = "Please enter Price";
                }
                else
                {
                    if (!float.TryParse(this.priceValue.Text, out price))
                    {
                        error = "Invalid price";
                    }
                    else
                    {
                        if (price <= 0)
                        {
                            error = "The price must be greater than 0.";
                        }
                    }
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.unitID.Text == null)
                {
                    error = "Please enter Unit ID";
                }
                else
                {
                    unit = this.unitID.Text;
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.currencySymbol == null)
                {
                    error = "Please enter currency";
                }
                else
                {
                    currency = this.currencySymbol.Text;
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.deliveryDate.EditValue == null)
                {
                    error = "Please select Delivery Date";
                }
                else
                {
                    DateTime.TryParse(deliveryDate.EditValue.ToString(), out deliverydate);
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.transportationID.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)this.transportationID.SelectedItem;
                    transport = item.Value.ToString();
                }

                if (this.deliveryID.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)this.deliveryID.SelectedItem;
                    delivery = item.Value.ToString();
                }
                partno = this.partNo.Text;
                purpose = this.purposeValue.Text;
                note = this.noteValue.Text;

                try
                {
                    ////////////////////////////////////////////////////////////
                    /// Update PODetails
                    ////////////////////////////////////////////////////////////
                    string new_suffix = poid.Substring(poid.Length - 3, 2);
                    int index = Int32.Parse(new_suffix);
                    index++;
                    string new_poid = poid.Substring(0, poid.Length - 3) + index.ToString("D2") + "F";
                    string sql = null;
                    bool conflict = false;

                    
                    myConnection.Open();                    
                    SqlCommand cmd_sql = myConnection.CreateCommand();

                    // Check if there is PO for new poid
                    sql = "SELECT COUNT(ID) FROM [241_MasterPO] WHERE ID='" + new_poid + "'";
                    cmd_sql = new SqlCommand(sql, myConnection);
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader[0] != DBNull.Value)
                        {
                            if (Int32.Parse(dataReader[0].ToString()) > 0)
                            {
                                conflict = true;                                
                            }
                        }
                    }
                    dataReader.Dispose();
                    cmd_sql.Dispose();

                    if (!conflict)
                    {
                        // Get data of current PO
                        string sql_update = null;
                        string sql_insert = null;
                        sql = "SELECT [ID], [InputDate], [SupplierID], [NameOfCommodity], [DeliveryTermID],"
                            + " [PaymentID], [Remark], [ShipToCode]"
                            + " FROM [241_MasterPO]"
                            + " WHERE ID='" + poid + "'";
                        cmd_sql = new SqlCommand(sql, myConnection);
                        dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            sql_update = "UPDATE [241_MasterPO] SET Change=1, Finished=1, Note='Changed' WHERE ID='" + poid + "'";
                            sql_insert = "INSERT INTO [241_MasterPO] ([ID], [InputDate], [SupplierID], [NameOfCommodity],"
                                + " [DeliveryTermID], [PaymentID], [Remark], [ShipToCode])"
                                + " VALUES ('" + new_poid + "', @inputdate,"
                                + " '" + dataReader["SupplierID"].ToString() + "',"
                                + " '" + dataReader["NameOfCommodity"].ToString() + "',"
                                + " '" + dataReader["DeliveryTermID"].ToString() + "',"
                                + " '" + dataReader["PaymentID"].ToString() + "',"
                                + " '" + dataReader["Remark"].ToString() + "',"                                
                                + " '" + dataReader["ShipToCode"].ToString() + "')";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();

                        // Update MasterPO to change state
                        cmd_sql = new SqlCommand(sql_update, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        // Insert new MasterPO with new POID
                        cmd_sql = new SqlCommand(sql_insert, myConnection);
                        cmd_sql.Parameters.Add("@inputdate", SqlDbType.DateTime).Value = DateTime.Now.Date;
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        // Update old record on MasterPODetails to change state                        
                        sql = "UPDATE [241_MasterPODetails] SET Change=1"
                            + " WHERE POID='" + poid + "' AND OrderNo='" + order + "' AND Change=0 AND Cancelled=0";
                        cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        // Insert new record on MasterPODetails
                        sql = "INSERT INTO [241_MasterPODetails]"
                            + " (POID, OrderNo, PartNo, Quantity, UnitID, UnitPrice, CurrencyID, DeliveryDate, TransportID,"
                            + " DeliveryTermID, Note, Purpose) VALUES ('" + new_poid + "', '" + order + "', N'" + partno + "',"
                            + " " + quantity + ", '" + unit + "', " + price + ", '" + currency + "', @deliverydate,"
                            + " '" + transport + "', '" + delivery + "', '" + noteValue.Text + "', '" + purposeValue.Text + "')";
                        cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.Parameters.Add("@deliverydate", SqlDbType.DateTime).Value = deliverydate.Date;
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        // Update all other MasterPODetails to new POID
                        sql = "UPDATE [241_MasterPODetails] SET POID='" + new_poid + "'"
                            + " WHERE POID='" + poid + "' AND Change=0 AND Cancelled=0";
                        cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        MessageBox.Show("MasterPODetails updated success.");
                        this.Dispose();
                    }
                    else
                    {
                        MessageBox.Show("Conflict POID. Please try again");
                    }
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot update MasterPODetails. Please try again.");
                }
            }           
        }
    }
}
